--------------------------------------------------------------------------------------------
--Member 테이블 
--------------------------------------------------------------------------------------------
create table MEMBER(
id varchar2(30) primary key,
password varchar2(30) not null,
name varchar2(30) not null,
tel varchar2(30) not null,
email varchar2(50) not null,
address varchar2(200) not null,
hint varchar2(500) not null,
answer varchar2(500) not null,
gender varchar2(5) not null,
userlevel number not null
);
drop table member;

select * from MEMBER;

insert into MEMBER(id, password, name, tel, email, address, hint, answer, gender,userlevel) 
values('admin','1234', '관리자', '012-3456-7890','admin@adddd.com','판교', '너의 집은 어디?', '판교!', '남',0);

--test용 member
insert into MEMBER(id, password, name, tel, email, address, hint, answer, gender,userlevel) 
values('9','1234', '남_테스트7', '012-3456-7890','admin@adddd.com','판교', '너의 집은 어디?', '판교!', 'man',1);

insert into MEMBER(id, password, name, tel, email, address, hint, answer, gender,userlevel) 
values('10','1234', '여_테스트8', '012-3456-7890','admin@adddd.com','판교', '너의 집은 어디?', '판교!', 'woman',1);

delete MEMBER where id='2';
--------------------------------------------------------------------------------------------
--공지사항 테이블 
--------------------------------------------------------------------------------------------
create table noticeBoard(
noticeno number primary key,
noticetitle varchar2(250) not null,
noticeContent varchar2(4000) not null, 
noticeCount number not null,
noticeWriteDate date not null,
id varchar2(30) not null constraint fk_noticeId references MEMBER
);
create sequence noticeBoard_seq nocache;

select * from noticeBoard

drop table noticeBoard
drop sequence noticeBoard_seq

--------------------------------------------------------------------------------------------
--QnA 테이블 
create sequence QnABoard_seq nocache;
select QnABoard_seq.nextval from dual;
drop sequence QnABoard_seq;
select * from QNABOARD;
drop table QnABoard;
--------------------------------------------------------------------------------------------
create table QnABoard(
qnaNo number primary key,
qnaPassword varchar2(50) not null,
qnaTitle varchar2(250) not null,
qnaContent varchar2(4000) not null,
qnaDate date not null,
qnaCount number not null,
qnaRef number not null,
qnaRestep number not null,
qnaRelevel number not null,
id varchar2(30) constraint fk_QnAId references MEMBER
);
insert into QNABOARD(qnaNo,qnaTitle,qnaPassword,qnaContent,qnaDate,qnaCount,qnaRef,qnaRestep,qnaRelevel,id)
values(999, '2323', 'fgfg', 'ghgh', sysdate, 0,5, 0, 0, 'admin')
--------------------------------------------------------------------------------------------
--LETTER 테이블 
--------------------------------------------------------------------------------------------
drop table LETTER;
create table LETTER( 
letterNo number primary key,
letterTitle varchar2(50) not null, 
letterWriteDate date not null, 
letterContent varchar2(2000) not null, 
letterBackGround varchar2(30) not null,
id varchar2(30) not null constraint fk_id references member,
optionalNo number not null constraint fk_optionalNo references optionalService 
);

create sequence letter_seq nocache;
drop sequence letter_seq;
drop table letter
select * from letter

--------------------------------------------------------------------------------------------
--optionalService테이블 
create sequence optionalService_seq nocache;
drop sequence optionalService_seq;
select optionalService_seq.nextval from dual;
--------------------------------------------------------------------------------------------
create table optionalService(
optionalNo number primary key,
mId varchar2(30) not null,
wId varchar2(30) not null,
responseAdmin number default 0, -- 0은 비승인 1은 승인
requestM number default 0, -- 0은 비승인 1은 승인
requestW number default 0, -- 0은 비승인 1은 승인
rePassword varchar2(30) not null,
serviceCloseDate date not null
--letterNo number not null constraint fk_letterNo references LETTER 
);

drop table optionalService;
delete OPTIONALSERVICE where wId='1';
update OPTIONALSERVICE set responseAdmin = '1' where optionalNo='8'
drop table optionalService

select * from OPTIONALSERVICE

delete OPTIONALSERVICE where optionalNo = '8'

update OPTIONALSERVICE set responseAdmin ='1' where optionalNo='6'

select id, name, tel, address, gender from member where id='admin'


select ADD_MONTHS(sysdate,12) from dual;
---------------------------------------
--test
---------------------------------------
create table happytime(
	no number primary key,
	info varchar2(50) not null,
	fileName varchar2(100) not null
)
drop table happytime
insert into happytime(no,info,fileName) values(1,'커플1','bear1.jpg');
insert into happytime(no,info,fileName) values(2,'커플2','bear2.jpg');
insert into happytime(no,info,fileName) values(3,'커플3','bear3.jpg');
insert into happytime(no,info,fileName) values(4,'커플4','bear4.jpg');
insert into happytime(no,info,fileName) values(5,'커플5','bear5.jpg');
insert into happytime(no,info,fileName) values(6,'커플6','bear6.jpg');
insert into happytime(no,info,fileName) values(6,'커플7','bear7.jpg');
insert into happytime(no,info,fileName) values(6,'커플8','bear8.jpg');
insert into happytime(no,info,fileName) values(6,'커플9','bear9.jpg');
insert into happytime(no,info,fileName) values(6,'커플10','bear10.jpg');
insert into happytime(no,info,fileName) values(6,'커플6','bear6.jpg');
select * from happytime